package cn.milk.dao;

import cn.milk.model.Menu;
import cn.milk.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MenuDAO {

    public Menu getMenuById(int id) throws SQLException {
        String sql = "SELECT * FROM menu WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                Menu menu = new Menu();
                menu.setId(rs.getInt("id"));
                menu.setName(rs.getString("name"));
                menu.setCategoryid(rs.getInt("categoryid"));
                menu.setCategoryname(rs.getString("categoryname"));
                menu.setPrice(rs.getDouble("price"));
                menu.setPic(rs.getString("pic"));
                menu.setDescription(rs.getString("description"));
                return menu;
            }
        }
        return null; // 如果没有找到记录，则返回 null
    }
    public List<String> getMenus() throws SQLException {
        List<String> menus = new ArrayList<>();
        String sql = "SELECT id, name FROM menu";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                menus.add(rs.getString("name"));
            }
        }
        return menus;
    }

    public List<Menu> getMenuList() throws SQLException {
        List<Menu> menus = new ArrayList<>();
        String sql = "SELECT id, name, categoryid, categoryname, price, pic, description FROM menu";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Menu menu = new Menu();
                menu.setId(rs.getInt("id"));
                menu.setName(rs.getString("name"));
                menu.setCategoryid(rs.getInt("categoryid"));
                menu.setCategoryname(rs.getString("categoryname"));
                menu.setPrice(rs.getDouble("price"));
                menu.setPic(rs.getString("pic"));
                menu.setDescription(rs.getString("description"));
                menus.add(menu);
            }
        }
        return menus;
    }


    public String getMenunameById(int menuId) throws SQLException {
        String sql = "SELECT name FROM menu WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, menuId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getString("name");
            }
        }
        return null;
    }

    public double getPriceById(int menuId) throws SQLException {
        String sql = "SELECT price FROM menu WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, menuId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getDouble("price");
            }
        }
        return 0.0;
    }

    public List<Menu> getMenus(String search, int page, int pageSize) throws SQLException {
        List<Menu> menus = new ArrayList<>();
        String sql = "SELECT * FROM menu WHERE name LIKE ? LIMIT ?, ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%" + search + "%");
            stmt.setInt(2, (page - 1) * pageSize);
            stmt.setInt(3, pageSize);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Menu menu = new Menu();
                menu.setId(rs.getInt("id"));
                menu.setName(rs.getString("name"));
                menu.setCategoryid(rs.getInt("categoryid"));
                menu.setCategoryname(rs.getString("categoryname"));
                menu.setPrice(rs.getDouble("price"));
                menu.setPic(rs.getString("pic"));
                menu.setDescription(rs.getString("description"));
                menus.add(menu);
            }
        }
        return menus;
    }

    public int getTotalCount(String search) throws SQLException {
        String sql = "SELECT COUNT(*) FROM menu WHERE name LIKE ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%" + search + "%");
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
        return 0;
    }

    public void addMenu(Menu menu) throws SQLException {
        String sql = "INSERT INTO menu (name, categoryid, categoryname, price, pic, description) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, menu.getName());
            stmt.setInt(2, menu.getCategoryid());
            stmt.setString(3, menu.getCategoryname());
            stmt.setDouble(4, menu.getPrice());
            stmt.setString(5, menu.getPic());
            stmt.setString(6, menu.getDescription());
            stmt.executeUpdate();
        }
    }

    public void updateMenu(Menu menu) throws SQLException {
        String sql = "UPDATE menu SET name = ?, categoryid = ?, categoryname = ?, price = ?, pic = ?, description = ? WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, menu.getName());
            stmt.setInt(2, menu.getCategoryid());
            stmt.setString(3, menu.getCategoryname());
            stmt.setDouble(4, menu.getPrice());
            stmt.setString(5, menu.getPic());
            stmt.setString(6, menu.getDescription());
            stmt.setInt(7, menu.getId());
            stmt.executeUpdate();
        }
    }

    public void deleteMenu(int id) throws SQLException {
        String sql = "DELETE FROM menu WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            stmt.executeUpdate();
        }
    }
}
